﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
/*
 * 1. 完成Excel的数据读取
 * 
 */
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace HousingDistribution
{
    class ExcelReader
    {
        public DataSet Read(string filePath)
        {
            string excel2000_2003ExtName = ".xls";
            string excel2007_2010ExtName = ".xlsx";

            string strConn;
            if (filePath.EndsWith(excel2000_2003ExtName))
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            }
            else if (filePath.EndsWith(excel2007_2010ExtName))
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            }
            else
            {
                System.Console.WriteLine("文件格式错误");
                return null;
            }

            OleDbConnection conn = new OleDbConnection(strConn);

            conn.Open();

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            DataSet excelDataSet = new DataSet();
            
            for (int i = 0; i < dtSheetName.Rows.Count; i++)
            {
                //下面这句查询第一个表的名字
                string tableName = dtSheetName.Rows[i]["Table_Name"].ToString().Trim();

                string type = DataDictionary.Instance().GetDataTypeBySheetName(tableName);

                //sheet name一旦对应，则认为是有效数据
                if (type != null)
                {
                    string strExcel = "select * from [" + tableName + "]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);

                    try
                    {
                        DataTable dt = excelDataSet.Tables.Add();
                        dt.TableName = type;

                        myCommand.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("文件格式错误：" + ex.Message);
                        return null;
                    }
                }
            }

            conn.Close();

            return excelDataSet;
        }
    }
}
